package cn.zime.iot.service.impl;

import cn.zime.iot.entity.StudentDO;
import cn.zime.iot.req.StudentRequest;
import cn.zime.iot.res.TableDTO;
import cn.zime.iot.service.StudentService;
import cn.zime.iot.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;


public class StudentServiceImpl implements StudentService {
    @Override
    public TableDTO retrieveStudents(StudentRequest request) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from student ");
        if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
            sql.append(" where name like '%").append(request.getSearchKey().trim()).append("%'");
        }
        sql.append("order by id desc limit ").append(request.getStart()).append(",")
                .append(request.getPageSize());
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        TableDTO returnDTO = new TableDTO();
        try {
            conn = DBUtil.getConn();
            assert conn != null;
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            //查询记录
            returnDTO.setData(fillData(rs));

            sql.setLength(0);
            sql.append("select count(*) from student ");
            if (request.getSearchKey() != null && !"".equals(request.getSearchKey().trim())) {
                sql.append(" where name like '%").append(request.getSearchKey().trim()).append("%'");
            }
            ps = conn.prepareStatement(sql.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                int count = rs.getInt(1);
                returnDTO.setTotalCount(count);
            }
            return returnDTO;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public boolean add(StudentDO studentDO) {
        StringBuilder sql = new StringBuilder();
        sql.append(" insert into student(name,no,home_town,math,english,java,mysql,cisco) ");
        sql.append(" values(?,?,?,?,?,?,?,?) ");
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConn();
            assert conn != null;
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1,studentDO.getName());
            ps.setString(2,studentDO.getNo());
            ps.setString(3,studentDO.getHomeTown());
            ps.setDouble(4,studentDO.getMath());
            ps.setDouble(5,studentDO.getEnglish());
            ps.setDouble(6,studentDO.getJava());
            ps.setDouble(7,studentDO.getMysql());
            ps.setDouble(8,studentDO.getCisco());

            return ps.executeUpdate() == 1;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

    @Override
    public StudentDO getById(int selectedStudentId) {
        StringBuilder sql = new StringBuilder("select * from student where id = ? ");
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        StudentDO studentDO = new StudentDO();
        try {
            conn = DBUtil.getConn();
            assert conn != null;
            ps = conn.prepareStatement(sql.toString());
            ps.setInt(1,selectedStudentId);
            rs = ps.executeQuery();
            while (rs.next()) {
                //处理记录
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String no = rs.getString("no");
                String homeTown = rs.getString("home_town");
                double math = rs.getDouble("math");
                double english = rs.getDouble("english");
                double java = rs.getDouble("java");
                double mysql = rs.getDouble("mysql");
                double cisco = rs.getDouble("cisco");
                studentDO.setId(id);
                studentDO.setName(name);
                studentDO.setNo(no);
                studentDO.setHomeTown(homeTown);
                studentDO.setMath(math);
                studentDO.setEnglish(english);
                studentDO.setJava(java);
                studentDO.setMysql(mysql);
                studentDO.setCisco(cisco);
            }
            return studentDO;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeRs(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public boolean update(StudentDO studentDO) {
        StringBuilder sql = new StringBuilder();
        sql.append(" update student set name = ?,no=?,home_town=?,math= ?,english= ?,java= ?,mysql= ?,cisco= ? ");
        sql.append(" where id =? ");
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConn();
            assert conn != null;
            ps = conn.prepareStatement(sql.toString());
            ps.setString(1,studentDO.getName());
            ps.setString(2,studentDO.getNo());
            ps.setString(3,studentDO.getHomeTown());
            ps.setDouble(4,studentDO.getMath());
            ps.setDouble(5,studentDO.getEnglish());
            ps.setDouble(6,studentDO.getJava());
            ps.setDouble(7,studentDO.getMysql());
            ps.setDouble(8,studentDO.getCisco());
            ps.setInt(9,studentDO.getId());
            return ps.executeUpdate() == 1;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

    @Override
    public boolean delete(int[] selectedStudentIds) {
        StringBuilder sql = new StringBuilder();
        sql.append(" delete from student where id in ( ");
        int length = selectedStudentIds.length;
        for (int i = 0; i < length; i++) {
            if (i == (length - 1)) {
                sql.append(" ? ");
            }else {
                sql.append(" ?, ");
            }
        }
        sql.append(" ) ");
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtil.getConn();
            assert conn != null;
            ps = conn.prepareStatement(sql.toString());
            for (int i = 0; i < length; i++) {
                ps.setInt(i + 1, selectedStudentIds[i]);
            }
            return ps.executeUpdate() == length;
        }catch (Exception e) {
            e.printStackTrace();
        }finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return false;
    }

    private Vector<Vector<Object>> fillData(ResultSet rs) throws SQLException {
        Vector<Vector<Object>> data = new Vector<>();
        while (rs.next()) {
            //处理记录
            Vector<Object> oneRecord = new Vector<>();
            int id = rs.getInt("id");
            String name = rs.getString("name");
            String no = rs.getString("no");
            String homeTown = rs.getString("home_town");
            double math = rs.getDouble("math");
            double english = rs.getDouble("english");
            double java = rs.getDouble("java");
            double mysql = rs.getDouble("mysql");
            double cisco = rs.getDouble("cisco");
            double totalScore = math+english+java+mysql+cisco;
            oneRecord.addElement(id);
            oneRecord.addElement(name);
            oneRecord.addElement(no);
            oneRecord.addElement(homeTown);
            oneRecord.addElement(math);
            oneRecord.addElement(english);
            oneRecord.addElement(java);
            oneRecord.addElement(mysql);
            oneRecord.addElement(cisco);

            oneRecord.addElement(totalScore);
            data.addElement(oneRecord);
        }
        return data;
    }
}
